/*
 * Created on 2006-2-18
 *
 * TODO To change the template for this generated file go to
 * Window - Preferences - Java - Code Style - Code Templates
 */
package com.hitrust.gs.net;

import java.io.File;
import jxl.*;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.*;

import com.hitrust.gs.util.exception.GSException;
/**
 * @author simonyi
 *
 * TODO To change the template for this generated type comment go to
 * Window - Preferences - Java - Code Style - Code Templates
 */
public class GSAuditManager {

    public File getAuditFile(String fileNameWithPath){
        File f = new File(fileNameWithPath);
        
        return f;
    }
    
    public void delAuditFile(String date,int category) throws GSException {
        
        File f = null; 
        f = GSAuditConstants.getFile(date,category);
        f.delete();
    }
    

    public void recordDownloadFailed(String date,int category) throws GSException {
        GSAuditDAO dao = new GSAuditDAO();
        dao.recordDownloadFailed(date,category);
    }
    
    public void recordDownloadSucceed(String date,int category) throws GSException {
        GSAuditDAO dao = new GSAuditDAO();
        dao.recordDownloadSucceed(date,category);
        
    }
    
    public void clearOverdueMsg(String date,int category) throws GSException {
        new GSAuditDAO().clearOverdueMsg(date,category);
    }
    
    public Object getAuditResult(String date) throws GSException {
        return new GSAuditDAO().getAuditResult(date);
    }

    public ArrayList getAuditMsg(String date,String category) throws GSException {
        return new GSAuditDAO().getAuditMsg(date,category);
    }
    
    /**
     * 从excel文件读取集中采购系统提交给采购网的订单明细信息
     * @param tFile
     * @return 返回一个长度为2的对象数组，其第一个元素为统计信息数组，第二个元素为一个以订单详细信息Bean为元素的哈希表
     */
    public Object getAllItems(File tFile) throws GSException{
        Object[] obj = null;
        try{
            //开始读取excel文件
            Workbook tBook = Workbook.getWorkbook(tFile);
            //读取sheet表单
            Sheet tSheet = tBook.getSheet(0);
            
            Cell[] frCells = tSheet.getRow(0);
            //xls文件，只有5列
            int index = 0;
            HashMap map = new HashMap();
            while(true){                
                Cell[] cells = tSheet.getRow(++index);
                if("GrandTotal".equalsIgnoreCase(cells[0].getContents())){
                    break;
                }
                GSAuditBean tBean = new GSAuditBean();
                tBean.setOrderDetailID(cells[0].getContents());
                tBean.setManufactureCode(cells[1].getContents());
                tBean.setOrderCount(cells[2].getContents());
                tBean.setManufactureUnitPrice(cells[3].getContents());
                tBean.setManufactureTotalPrice(cells[4].getContents());
                if(tBean.getOrderDetailID() != null && tBean.getOrderDetailID().length() != 0)
                    map.put(cells[0].getContents(),tBean);
            }
            Cell[] totalMsgCell = tSheet.getRow(1+index); 
            String[] orderMsg = {totalMsgCell[0].getContents(),totalMsgCell[2].getContents(),totalMsgCell[4].getContents()};
            
            obj = new Object[]{orderMsg,map};
        }
        catch(Exception e){
            throw new GSException(GSException.E17790,GSException.E17790_MSG);
        }
        return obj;
    }
    
    /**
     * 从excel文件读取采购网回复集中采购系统的订单明细送货确认信息
     * @param tFile
     * @return 返回一个长度为2的对象数组，其第一个元素为统计信息数组，第二个元素为一个以订单详细信息Bean为元素的哈希表
     */
    public Object getItemsDoing(File tFile) throws GSException{
        Object[] obj = null;
        try{
            //开始读取excel文件
            Workbook tBook = Workbook.getWorkbook(tFile);
            //读取sheet表单
            Sheet tSheet = tBook.getSheet(0);
            
            Cell[] frCells = tSheet.getRow(0);
            //xls文件，只有5列
            int index = 0;
            HashMap map = new HashMap();
            while(true){                
                Cell[] cells = tSheet.getRow(++index);
                if("GrandTotal".equalsIgnoreCase(cells[0].getContents())){
                    break;
                }
                GSAuditBean tBean = new GSAuditBean();
                tBean.setOrderDetailID(cells[0].getContents());
                tBean.setManufactureCode(cells[1].getContents());
                tBean.setSendCount(cells[2].getContents());
                tBean.setManufactureUnitPrice(cells[3].getContents());
                tBean.setManufactureTotalPrice(cells[4].getContents());
                map.put(cells[0].getContents(),tBean);
            }
            Cell[] totalMsgCell = tSheet.getRow(1+index); 
            String[] orderMsg = {totalMsgCell[0].getContents(),totalMsgCell[2].getContents(),totalMsgCell[4].getContents()};
            
            obj = new Object[]{orderMsg,map};
        }
        catch(Exception e){
            throw new GSException(GSException.E17790,GSException.E17790_MSG);
        }
        return obj;
    }
    
    /**
     * 从excel文件读取采购网回复集中采购系统的订单明细交货确认信息
     * @param tFile
     * @return 返回一个长度为2的对象数组，其第一个元素为统计信息数组，第二个元素为一个以订单详细信息Bean为元素的哈希表
     */
    public Object getItemsFinished(File tFile) throws GSException{
        Object[] obj = null;
        try{
            //开始读取excel文件
            Workbook tBook = Workbook.getWorkbook(tFile);
            //读取sheet表单
            Sheet tSheet = tBook.getSheet(0);
            
            Cell[] frCells = tSheet.getRow(0);
            //xls文件，只有5列
            int index = 0;
            HashMap map = new HashMap();
            while(true){                
                Cell[] cells = tSheet.getRow(++index);
                if("GrandTotal".equalsIgnoreCase(cells[0].getContents())){
                    break;
                }
                GSAuditBean tBean = new GSAuditBean();
                tBean.setOrderDetailID(cells[0].getContents());
                tBean.setManufactureCode(cells[1].getContents());
                tBean.setReceiveCount(cells[2].getContents());
                tBean.setManufactureUnitPrice(cells[3].getContents());
                tBean.setManufactureTotalPrice(cells[4].getContents());
                map.put(cells[0].getContents(),tBean);
            }
            Cell[] totalMsgCell = tSheet.getRow(1+index); 
            String[] orderMsg = {totalMsgCell[0].getContents(),totalMsgCell[2].getContents(),totalMsgCell[4].getContents()};
            
            obj = new Object[]{orderMsg,map};
        }
        catch(Exception e){
            throw new GSException(GSException.E17790,GSException.E17790_MSG);
        }
        return obj;
    }
    
    /**
     * 从excel文件读取采购网回复集中采购系统的订单明细送货拒绝信息
     * @param tFile
     * @return
     * @throws GSException
     */
    public Object getItemsRefused(File tFile) throws GSException{
        return this.getAllItems(tFile);
    }

    /**
     * 从excel文件读取采购网回复集中采购系统的订单明细送货取消信息
     * @param tFile
     * @return
     * @throws GSException
     */
    public Object getItemsCancel(File tFile) throws GSException{
        return this.getAllItems(tFile);
    }
    
    /**
     * 比较集中采购系统的订单明细和采购网回复的订单明细是否匹配
     * @param a
     * @param b
     * @param category
     * @return
     */
    public boolean isEqual(GSAuditBean a,GSAuditBean b,int category){
        if(a == null || b == null )
            return false;
        boolean returnValue = true;
        //验证产品编号
        if(!a.getManufactureCode().equals(b.getManufactureCode()))
            return false;
        switch(category){
        case 1:
            //验证订货数量
            if(!a.getOrderCount().equals(b.getOrderCount()))
                return false;
            break;
        case 2:
            //验证发货数量
            if(!a.getSendCount().equals(b.getSendCount()))
                return false;
            break;
        case 3:
            //验证拒绝数量(订货数量)
            if(!a.getOrderCount().equals(b.getOrderCount()))
                return false;
            break;
        case 4:
            //验证交货数量
            if(!a.getReceiveCount().equals(b.getReceiveCount()))
                return false;
            break;
        case 5:
            //验证取消数量
            if(!a.getOrderCount().equals(b.getOrderCount()))
                return false;
            break;
        }
        //验证单价
        if(!a.getManufactureUnitPrice().equals(b.getManufactureUnitPrice()))
            return false;
        //验证总价
        if(!a.getManufactureTotalPrice().equals(b.getManufactureTotalPrice()))
            return false;
        
        return returnValue;
    }
    
    /**
     * 执行匹配判断并记录不匹配的数据到数据库(订单明细)
     * @param tFile
     * @param category
     * @throws GSException
     */
    public void auditAllItems(File tFile,String date) throws GSException {

        int category = 1;
        GSAuditDAO d = new GSAuditDAO();
        Object[] obj = null;

        obj = (Object[])getAllItems(tFile);
        
        String[] statistic = (String[])obj[0]; 
        HashMap map = (HashMap)obj[1];

        Connection conn = null;
        //是否完全匹配判断参数
        boolean isAllMatch = true;
        try{
            conn = d.getConnetion();
            conn.setAutoCommit(false);

            ArrayList ary = d.queryAuditDetail(conn,date,9);
            
            int count = 0;
            double price = 0;
            int size = ary.size();
            //对集中采购平台的所有明细做过滤，记录不匹配的记录，如果有明细在采购网没有记录，也记录下来(bBean=null)
            //同时，清除采购网Map中已经做过匹配处理的记录，留下来的未匹配记录，单独处理
            for(int i=0;i<ary.size();i++){
                GSAuditBean aBean = (GSAuditBean)ary.get(i);                
                String detailCode = aBean.getOrderDetailID();
                GSAuditBean bBean = (GSAuditBean)map.get(detailCode);
                
                if(!this.isEqual(aBean,bBean,category)){
                    //记录不匹配的行到对帐信息表
                    d.recordAuditNotMatchMsg(conn,aBean,bBean,date,category);
                    //有一行不匹配,则不是完全匹配
                    isAllMatch = false;
                }
                count += Integer.parseInt(aBean.getOrderCount());
                price += Double.parseDouble(aBean.getManufactureTotalPrice()==null?"0":aBean.getManufactureTotalPrice());
                map.remove(detailCode);
            }
            //采购网在集中采购平台没有匹配记录的，也记录下来(aBean=null)
            Iterator iter = map.values().iterator();
            while(iter.hasNext()){
                isAllMatch = false;
                GSAuditBean bBean = (GSAuditBean)iter.next();
                d.recordAuditNotMatchMsg(conn,null,bBean,date,category);
            }
            
            if(!isAllMatch){//如果没有完全匹配，则记录该不匹配结果到对帐结果表
                d.recordAuditNotMatchResult(conn,date,category);
            }
            else{//如果完全匹配，则记录匹配结果到对帐结果表
                d.recordAuditMatchResult(conn,date,category);
            }            
            //无论匹配与否，记录统计数据到对帐结果表
            d.recordAuditStat(conn,date,category,count,price,statistic,size);
            conn.commit();
            
        }catch(SQLException sqle){
            try{
                conn.rollback();    
            }catch(SQLException sqlex){
                
            }            
            throw new GSException (GSException.E19998,GSException.E19998_MSG);
        }
        finally{
            try{
                conn.setAutoCommit(true);
            }catch(SQLException sqle){}
            GSAuditDAO.close(conn);
        }
    }
    
    
    /**
     * 执行匹配判断并记录不匹配的数据到数据库(订单送货明细)
     * @param tFile
     * @param category
     * @throws GSException
     */
    public void auditItemsDoing(File tFile,String date) throws GSException {
        int category = 2;
        GSAuditDAO d = new GSAuditDAO();
        Object[] obj = null;

        obj = (Object[])getItemsDoing(tFile);

        
        String[] statistic = (String[])obj[0]; 
        HashMap map = (HashMap)obj[1];

        Connection conn = null;
        //是否完全匹配判断参数
        boolean isAllMatch = true;
        try{
            conn = d.getConnetion();
            conn.setAutoCommit(false);

            ArrayList ary = d.queryAuditDetail(conn,date,10);
            int count = 0;
            double price = 0;
            int size = ary.size();
            //对集中采购平台的所有明细做过滤，记录不匹配的记录，如果有明细在采购网没有记录，也记录下来(bBean=null)
            //同时，清除采购网Map中已经做过匹配处理的记录，留下来的未匹配记录，单独处理
            for(int i=0;i<ary.size();i++){
                GSAuditBean aBean = (GSAuditBean)ary.get(i);                
                String detailCode = aBean.getOrderDetailID();
                GSAuditBean bBean = (GSAuditBean)map.get(detailCode);
                
                if(!this.isEqual(aBean,bBean,category)){
                    //记录不匹配的行到对帐信息表
                    d.recordAuditNotMatchMsg(conn,aBean,bBean,date,category);
                    //有一行不匹配,则不是完全匹配
                    isAllMatch = false;
                }
                count += Integer.parseInt(aBean.getOrderCount());
                price += Double.parseDouble(aBean.getManufactureTotalPrice()==null?"0":aBean.getManufactureTotalPrice());
                map.remove(detailCode);
            }
            //采购网在集中采购平台没有匹配记录的，也记录下来(aBean=null)
            Iterator iter = map.values().iterator();
            while(iter.hasNext()){
                isAllMatch = false;
                GSAuditBean bBean = (GSAuditBean)iter.next();
                d.recordAuditNotMatchMsg(conn,null,bBean,date,category);
            }
            
            //如果没有完全匹配，则记录该不匹配结果到对帐结果表
            if(!isAllMatch){
                d.recordAuditNotMatchResult(conn,date,category);
            }
            //如果完全匹配，则记录匹配结果到对帐结果表
            else{
                d.recordAuditMatchResult(conn,date,category);
            }
            //无论匹配与否，记录统计数据到对帐结果表
            d.recordAuditStat(conn,date,category,count,price,statistic,size);
            conn.commit();
            
        }catch(SQLException sqle){
            try{
                conn.rollback();    
            }catch(SQLException sqlex){
                
            }            
            throw new GSException (GSException.E19998,GSException.E19998_MSG);
        }
        finally{
            try{
                conn.setAutoCommit(true);
            }catch(SQLException sqle){}
            GSAuditDAO.close(conn);
        }
    }
    
    /**
     * 执行匹配判断并记录不匹配的数据到数据库(订单拒绝明细)
     * @param tFile
     * @param category
     * @throws GSException
     */
    public void auditItemsRefused(File tFile,String date) throws GSException {
        int category = 3;
        GSAuditDAO d = new GSAuditDAO();
        Object[] obj = null;
        obj = (Object[])getItemsRefused(tFile);
        
        String[] statistic = (String[])obj[0]; 
        HashMap map = (HashMap)obj[1];

        Connection conn = null;
        //是否完全匹配判断参数
        boolean isAllMatch = true;
        try{
            conn = d.getConnetion();
            conn.setAutoCommit(false);
            
            ArrayList ary = d.queryAuditDetail(conn,date,11);

            int count = 0;
            double price = 0;
            int size = ary.size();
            //对集中采购平台的所有明细做过滤，记录不匹配的记录，如果有明细在采购网没有记录，也记录下来(bBean=null)
            //同时，清除采购网Map中已经做过匹配处理的记录，留下来的未匹配记录，单独处理
            for(int i=0;i<ary.size();i++){
                GSAuditBean aBean = (GSAuditBean)ary.get(i);                
                String detailCode = aBean.getOrderDetailID();
                GSAuditBean bBean = (GSAuditBean)map.get(detailCode);
                
                if(!this.isEqual(aBean,bBean,category)){
                    //记录不匹配的行到对帐信息表
                    d.recordAuditNotMatchMsg(conn,aBean,bBean,date,category);
                    //有一行不匹配,则不是完全匹配
                    isAllMatch = false;
                }
                count += Integer.parseInt(aBean.getOrderCount());
                price += Double.parseDouble(aBean.getManufactureTotalPrice()==null?"0":aBean.getManufactureTotalPrice());
                map.remove(detailCode);
            }
            //采购网在集中采购平台没有匹配记录的，也记录下来(aBean=null)
            Iterator iter = map.values().iterator();
            while(iter.hasNext()){
                isAllMatch = false;
                GSAuditBean bBean = (GSAuditBean)iter.next();
                d.recordAuditNotMatchMsg(conn,null,bBean,date,category);
            }
            
            //如果没有完全匹配，则记录该不匹配结果到对帐结果表
            if(!isAllMatch){
                d.recordAuditNotMatchResult(conn,date,category);
            }
            //如果完全匹配，则记录匹配结果到对帐结果表
            else{
                d.recordAuditMatchResult(conn,date,category);
            }
            //无论匹配与否，记录统计数据到对帐结果表
            d.recordAuditStat(conn,date,category,count,price,statistic,size);
            conn.commit();
            
        }catch(SQLException sqle){
            try{
                conn.rollback();    
            }catch(SQLException sqlex){
                
            }            
            throw new GSException (GSException.E19998,GSException.E19998_MSG);
        }
        finally{
            try{
                conn.setAutoCommit(true);
            }catch(SQLException sqle){}
            GSAuditDAO.close(conn);
        }
    }
    
    /**
     * 执行匹配判断并记录不匹配的数据到数据库(订单交货明细)
     * @param tFile
     * @param category
     * @throws GSException
     */
    public void auditItemsFinished(File tFile,String date) throws GSException {
        int category = 4;
        GSAuditDAO d = new GSAuditDAO();
        Object[] obj = null;
            obj = (Object[])getItemsFinished(tFile);
        
        String[] statistic = (String[])obj[0]; 
        HashMap map = (HashMap)obj[1];

        Connection conn = null;
        //是否完全匹配判断参数
        boolean isAllMatch = true;
        try{
            conn = d.getConnetion();
            conn.setAutoCommit(false);
            
            ArrayList ary = d.queryAuditDetail(conn,date,12);

            int count = 0;
            double price = 0;
            int size = ary.size();
            //对集中采购平台的所有明细做过滤，记录不匹配的记录，如果有明细在采购网没有记录，也记录下来(bBean=null)
            //同时，清除采购网Map中已经做过匹配处理的记录，留下来的未匹配记录，单独处理
            for(int i=0;i<ary.size();i++){
                GSAuditBean aBean = (GSAuditBean)ary.get(i);                
                String detailCode = aBean.getOrderDetailID();
                GSAuditBean bBean = (GSAuditBean)map.get(detailCode);
                
                if(!this.isEqual(aBean,bBean,category)){
                    //记录不匹配的行到对帐信息表
                    d.recordAuditNotMatchMsg(conn,aBean,bBean,date,category);
                    //有一行不匹配,则不是完全匹配
                    isAllMatch = false;
                }
                count += Integer.parseInt(aBean.getOrderCount());
                price += Double.parseDouble(aBean.getManufactureTotalPrice()==null?"0":aBean.getManufactureTotalPrice());
                map.remove(detailCode);
            }
            //采购网在集中采购平台没有匹配记录的，也记录下来(aBean=null)
            Iterator iter = map.values().iterator();
            while(iter.hasNext()){
                isAllMatch = false;
                GSAuditBean bBean = (GSAuditBean)iter.next();
                d.recordAuditNotMatchMsg(conn,null,bBean,date,category);
            }
            
            //如果没有完全匹配，则记录该不匹配结果到对帐结果表
            if(!isAllMatch){
                d.recordAuditNotMatchResult(conn,date,category);
            }
            //如果完全匹配，则记录匹配结果到对帐结果表
            else{
                d.recordAuditMatchResult(conn,date,category);
            }
            //无论匹配与否，记录统计数据到对帐结果表
            d.recordAuditStat(conn,date,category,count,price,statistic,size);
            conn.commit();
            
        }catch(SQLException sqle){
            try{
                conn.rollback();    
            }catch(SQLException sqlex){
                
            }            
            throw new GSException (GSException.E19998,GSException.E19998_MSG);
        }
        finally{
            try{
                conn.setAutoCommit(true);
            }catch(SQLException sqle){}
            GSAuditDAO.close(conn);
        }
    }

    /**
     * 执行匹配判断并记录不匹配的数据到数据库(订单拒绝明细)
     * @param tFile
     * @param category
     * @throws GSException
     */
    public void auditItemsCanceled(File tFile,String date) throws GSException {
        int category = 5;
        GSAuditDAO d = new GSAuditDAO();
        Object[] obj = null;
        obj = (Object[])this.getItemsCancel(tFile);
        
        String[] statistic = (String[])obj[0]; 
        HashMap map = (HashMap)obj[1];

        Connection conn = null;
        //是否完全匹配判断参数
        boolean isAllMatch = true;
        try{
            conn = d.getConnetion();
            conn.setAutoCommit(false);
            
            ArrayList ary = d.queryAuditDetail(conn,date,21);

            int count = 0;
            double price = 0;
            int size = ary.size();
            //对集中采购平台的所有明细做过滤，记录不匹配的记录，如果有明细在采购网没有记录，也记录下来(bBean=null)
            //同时，清除采购网Map中已经做过匹配处理的记录，留下来的未匹配记录，单独处理
            for(int i=0;i<ary.size();i++){
                GSAuditBean aBean = (GSAuditBean)ary.get(i);                
                String detailCode = aBean.getOrderDetailID();
                GSAuditBean bBean = (GSAuditBean)map.get(detailCode);
                
                if(!this.isEqual(aBean,bBean,category)){
                    //记录不匹配的行到对帐信息表
                    d.recordAuditNotMatchMsg(conn,aBean,bBean,date,category);
                    //有一行不匹配,则不是完全匹配
                    isAllMatch = false;
                }
                count += Integer.parseInt(aBean.getOrderCount());
                price += Double.parseDouble(aBean.getManufactureTotalPrice()==null?"0":aBean.getManufactureTotalPrice());
                map.remove(detailCode);
            }
            //采购网在集中采购平台没有匹配记录的，也记录下来(aBean=null)
            Iterator iter = map.values().iterator();
            while(iter.hasNext()){
                isAllMatch = false;
                GSAuditBean bBean = (GSAuditBean)iter.next();
                d.recordAuditNotMatchMsg(conn,null,bBean,date,category);
            }
            
            //如果没有完全匹配，则记录该不匹配结果到对帐结果表
            if(!isAllMatch){
                d.recordAuditNotMatchResult(conn,date,category);
            }
            //如果完全匹配，则记录匹配结果到对帐结果表
            else{
                d.recordAuditMatchResult(conn,date,category);
            }
            //无论匹配与否，记录统计数据到对帐结果表
            d.recordAuditStat(conn,date,category,count,price,statistic,size);
            conn.commit();
            
        }catch(SQLException sqle){
            try{
                conn.rollback();    
            }catch(SQLException sqlex){
                
            }  
            throw new GSException (GSException.E19998,GSException.E19998_MSG);
        }
        finally{
            try{
                conn.setAutoCommit(true);
            }catch(SQLException sqle){}
            GSAuditDAO.close(conn);
        }
    }
}


